****************************************************************************
****************************************************************************
***********RESEARCH PROJECT: IMMIGRATION AND OFFSHORING*********************
****************************************************************************
***************************AUTHORS:*****************************************
** Dario P.,****************************************************************
****************************************************************************



local stdate $S_DATE
local sttime $S_TIME

clear

capture log close


global prgrms "DO\"
global logs "LOG\"
global data "DATA\"
global tables "TABLE\"
global graph "GRAPH\"

set mem 5g

set matsize 5000

set more off

cd "G:\data\workdata\707562"


***idapall_eer2 is constructed from merging different ida data modules 


use ${data}idapall_eer_1990.dta, clear


drop skill2 skill3

sort hfaudd

merge hfaudd using ${data}udd.dta
drop _merge

gen edu=substr(hffsp,1,2)
destring edu, replace

gen skill1=(edu==20 | edu==25 | edu==35| edu==40)
gen skill2=(edu==50 | edu==60 | edu==65 | edu==70)
gen skill3=1 if skill1==0 & skill2==0
replace skill3=1 if edu==.


****keep a plausible age range
keep if age>16 & age<67

rename opr_land ieland
rename ie_type  ietype

keep  cvrnr  age  pnr year  ieland ietype akom skill1 skill2 skill3 pstill
bysort pnr year: gen id=_n
keep if id==1


destring akom, replace

recode akom (171=240) (181=230) (189=190) (205=230) (207=190) (208=210) (209=250) (211=260) (213=270) (215=270) (221=260) (225=250) /*
*/ (227=210)  (229=250) (231=219)  (235=240) (237=240) (251=350) (255=265) (257=350) (261=350) /*
*/ (263=265) (267=259) (271=336) (301=326) (303=340) (305=306) (307=370) (309=326) (311=330) (313=320) (315=316) (317=326) (319=326) /*
*/ (321=316) (323=326) (325=330) (327=306) (331=330) (333=330) (335=340) (337=340) (339=316)  (341=316) (343=306) /*
*/ (345=316) (351=320) (353=370) (355=360) (357=370) (359=360) (361=390) (363=360) (365=390) (367=360) (369=376) /*
*/ (371=376) (373=370) (375=376) (377=390) (379=360) (381=360) (383=360) (385=320) (387=376) (389=336) (391=376) (393=370) (395=376) /*
*/ (397=390) (401=400) (403=400) (405=400) (407=400) (409=400) (421=420) (423=480) (425=430) (427=479) (429=410) (431=430) (433=420) (435=479) (433=420) (435=479) (437=420) (439=440) /*
*/ (441=440) (443=492) (445=410) (447=440) (449=450) (451=410) (471=480) (473=430) (475=482) (477=430) (481=482) (483=480) (485=420) /*
*/ (487=482) (489=450) (491=420) (493=492) (495=450) (497=430) (499=420) (501=540) (503=580) (505=550) (507=540)  /*
*/ (511=510) (513=540) (515=510)  (517=550) (519=580) (521=550) (523=540) (527=575) (529=580) /*
*/ (531=550) (533=540) (535=540) (537=540) (539=580) (541=550) (543=510) (545=580) (551=530) (553=573) (555=573) (557=561) (559=575) (565=530) /*
*/ (567=561) (567=573) (569=575) (571=561) (577=573)  (603=630)  (609=615) (611=630) (613=766) (617=630) /*
*/ (619=766) (623=621) (625=756)  (629=621) (631=630) (651=657) (653=756) (655=760) (657=760) (659=760) (663=756) (667=760) (669=760) /*
*/ (673=665) (675=671) (677=657) (679=661) (681=760) (683=661) (685=657) (701=706) (703=746) (705=740) (709=710) (711=710) (713=710) (715=746) /*
*/  (721=706) (723=730) (725=707) (729=730) (731=730) (733=706) (735=707) (737=746) (739=706) (743=740) /*
*/ (745=746)  (749=740) (761=791) (763=791) (765=787) (767=710) (769=791) (771=740) (775=791) (777=779) (781=779) /*
*/ (783=779) (785=787)  (789=791)  (801=846) (803=849) (805=810) (807=810) (809=820) (811=849) (815=846) (817=851) /*
*/ (819=860) (821=860) (823=846) (827=820) (829=860) (831=851)  (835=849) (837=851) (839=860)  (841=813) (843=840) (845=840) (847=813) /*
*/ (861=820) if year<2006

**** 11 old municipalities (233, 509, 525, 601, 605, 627, 717, 719, 747, 793, 833)
***** were split into more municipalities: recode by exploiting the fact that dataset firm contains from 1998 on both the old and the new municipality kode, named
*****respectively GF_KOM_KODE and GF_KOM_KODE_2

destring cvrnr, replace
sort cvrnr year 
merge cvrnr year using ${data}akom2.dta 
drop _merge

bysort cvrnr: egen kom_ny=min( GF_KOM_KODE_2)

replace akom=kom_ny if (akom==233 | akom== 509 | akom==525 | akom==601 | akom== 605 | akom==627 | akom==717 | akom== 719 | akom==747 | akom==793 | akom== 833)


recode akom (147=101)


***drop those firms for which the municipality code cannot be identified

drop if akom==.



destring ieland, replace



gen natives=(ietype=="1") | ieland==5126| ieland==5130 | ieland==5180 | ieland==5134 | ieland==5142 | ieland==5150 | ieland==5108 | ieland==5140 | /*
*/ ieland==5156 | ieland==5164 | ieland==5170  | ieland==5182 | ieland==5104 | ieland==5120 



bysort akom: gen P_m88=_N


preserve
collapse P_m88, by(akom)
sort akom 
save ${data}pop_90.dta, replace
restore



bysort ieland: gen F_c88=_N 
bysort ieland akom: gen F_cm88=_N  if natives==0
bysort ieland: gen F_c88_ling=_N if (pstill=="35" | pstill=="36" | pstill=="37") & natives==0
replace F_c88_ling=0 if F_c88_ling==.
bysort ieland akom: gen F_cm88_ling=_N if (pstill=="35" | pstill=="36" | pstill=="37") & natives==0
replace F_cm88_ling=0 if F_cm88_ling==.
bysort ieland: gen F_c88_geo=_N if (pstill=="31" | pstill=="32" | pstill=="33" | pstill=="34") & natives==0
replace F_c88_geo=0 if F_c88_geo==.
bysort ieland akom: gen F_cm88_geo=_N if (pstill=="31" | pstill=="32" | pstill=="33" | pstill=="34") & natives==0
replace F_cm88_geo=0 if F_cm88_geo==.

gen s_cm88=F_cm88/F_c88
replace  s_cm88=0 if  s_cm88==.
gen s_cm88_ling=F_cm88_ling/F_c88_ling
replace  s_cm88_ling=0 if  s_cm88_ling==.
gen s_cm88_geo=F_cm88_geo/F_c88_geo
replace  s_cm88_geo=0 if  s_cm88_geo==.

preserve
collapse s_cm88  P_m88, by(akom ieland)
sort  akom ieland
save ${data}share_1990_no_eu_off.dta, replace
restore


preserve
collapse s_cm88_ling, by(akom ieland)
sum s_cm88_ling, det
sort akom ieland
save ${data}share_1990_no_eu_off_l.dta, replace
restore



collapse s_cm88_geo, by(akom ieland)
sum s_cm88_geo, det
sort akom ieland
save ${data}share_1990_no_eu_off_g.dta, replace




******DESTINATION SPECIFIC INSTRUMENT

******DESTINATION SPECIFIC INSTRUMENT

******DESTINATION SPECIFIC INSTRUMENT

******DESTINATION SPECIFIC INSTRUMENT

******DESTINATION SPECIFIC INSTRUMENT

******DESTINATION SPECIFIC INSTRUMENT


***calculate the current stock as in Peri and Foged (2013)


*******idapall3a is the matched employer-employee data with a few sample selection criteria (created from SAS file merging firm and IDA)
use ${data}idapall3a.dta, clear


sort hfaudd

merge hfaudd using ${data}udd.dta
drop _merge

gen edu=substr(hffsp,1,2)
destring edu, replace

gen skill1=(edu==20 | edu==25 | edu==35| edu==40)
gen skill2=(edu==50 | edu==60 | edu==65 | edu==70)
gen skill3=1 if skill1==0 & skill2==0
replace skill3=1 if edu==.

sort pnr year

merge pnr year using ${data}ie_type.dta

keep if _merge==3

drop _merge

rename opr_land ieland

rename ie_type  ietype


sort pnr year
merge pnr year using ${data}pstill.dta
keep if _merge==3
drop _merge

****keep a plausible age range
keep if age>16 & age<67



keep  akom cvrnr year ieland ietype skill1 skill2 skill3 pstill


destring akom, replace

recode akom (171=240) (181=230) (189=190) (205=230) (207=190) (208=210) (209=250) (211=260) (213=270) (215=270) (221=260) (225=250) /*
*/ (227=210)  (229=250) (231=219)  (235=240) (237=240) (251=350) (255=265) (257=350) (261=350) /*
*/ (263=265) (267=259) (271=336) (301=326) (303=340) (305=306) (307=370) (309=326) (311=330) (313=320) (315=316) (317=326) (319=326) /*
*/ (321=316) (323=326) (325=330) (327=306) (331=330) (333=330) (335=340) (337=340) (339=316)  (341=316) (343=306) /*
*/ (345=316) (351=320) (353=370) (355=360) (357=370) (359=360) (361=390) (363=360) (365=390) (367=360) (369=376) /*
*/ (371=376) (373=370) (375=376) (377=390) (379=360) (381=360) (383=360) (385=320) (387=376) (389=336) (391=376) (393=370) (395=376) /*
*/ (397=390) (401=400) (403=400) (405=400) (407=400) (409=400) (421=420) (423=480) (425=430) (427=479) (429=410) (431=430) (433=420) (435=479) (433=420) (435=479) (437=420) (439=440) /*
*/ (441=440) (443=492) (445=410) (447=440) (449=450) (451=410) (471=480) (473=430) (475=482) (477=430) (481=482) (483=480) (485=420) /*
*/ (487=482) (489=450) (491=420) (493=492) (495=450) (497=430) (499=420) (501=540) (503=580) (505=550) (507=540)  /*
*/ (511=510) (513=540) (515=510)  (517=550) (519=580) (521=550) (523=540) (527=575) (529=580) /*
*/ (531=550) (533=540) (535=540) (537=540) (539=580) (541=550) (543=510) (545=580) (551=530) (553=573) (555=573) (557=561) (559=575) (565=530) /*
*/ (567=561) (567=573) (569=575) (571=561) (577=573)  (603=630)  (609=615) (611=630) (613=766) (617=630) /*
*/ (619=766) (623=621) (625=756)  (629=621) (631=630) (651=657) (653=756) (655=760) (657=760) (659=760) (663=756) (667=760) (669=760) /*
*/ (673=665) (675=671) (677=657) (679=661) (681=760) (683=661) (685=657) (701=706) (703=746) (705=740) (709=710) (711=710) (713=710) (715=746) /*
*/  (721=706) (723=730) (725=707) (729=730) (731=730) (733=706) (735=707) (737=746) (739=706) (743=740) /*
*/ (745=746)  (749=740) (761=791) (763=791) (765=787) (767=710) (769=791) (771=740) (775=791) (777=779) (781=779) /*
*/ (783=779) (785=787)  (789=791)  (801=846) (803=849) (805=810) (807=810) (809=820) (811=849) (815=846) (817=851) /*
*/ (819=860) (821=860) (823=846) (827=820) (829=860) (831=851)  (835=849) (837=851) (839=860)  (841=813) (843=840) (845=840) (847=813) /*
*/ (861=820) if year<2006

**** 11 old municipalities (233, 509, 525, 601, 605, 627, 717, 719, 747, 793, 833)
***** were split into more municipalities: recode by exploiting the fact that dataset firm contains from 1998 on both the old and the new municipality kode, named
*****respectively GF_KOM_KODE and GF_KOM_KODE_2

destring cvrnr, replace
sort cvrnr year 
merge cvrnr year using ${data}akom2.dta 
drop _merge

bysort cvrnr: egen kom_ny=min( GF_KOM_KODE_2)

replace akom=kom_ny if (akom==233 | akom== 509 | akom==525 | akom==601 | akom== 605 | akom==627 | akom==717 | akom== 719 | akom==747 | akom==793 | akom== 833)


recode akom (147=101)


***drop those firms for which the municipality code cannot be identified

drop if akom==.

*****share of workers for different groups


destring ieland, replace



bysort year: gen total_empl=_N
bysort year akom: gen total_empl_mun=_N




gen natives=(ietype=="1") | ieland==5126| ieland==5130 | ieland==5180 | ieland==5134 | ieland==5142 | ieland==5150 | ieland==5108 | ieland==5140 | /*
*/ ieland==5156 | ieland==5164 | ieland==5170  | ieland==5182 | ieland==5104 | ieland==5120 


bysort ieland year: gen F_ct=_N if natives==0
replace F_ct=0 if F_ct==.
bysort ieland year: gen F_ct_ling=_N if (pstill=="35" | pstill=="36" | pstill=="37") & natives==0
replace F_ct_ling=0 if F_ct_ling==.
bysort ieland year: gen F_ct_geo=_N if natives==0 & (pstill=="31" | pstill=="32" | pstill=="33" | pstill=="34") 
replace F_ct_geo=0 if F_ct_geo==.
bysort ieland akom year: gen F_ct_mun=_N if natives==0
replace F_ct_mun=0 if F_ct_mun==.
gen share_for2=F_ct/total_empl
gen share_for2_mun=F_ct_mun/total_empl_mun

bysort ieland akom year: gen F_ct_ling_mun=_N if (pstill=="35" | pstill=="36" | pstill=="37") & natives==0
replace F_ct_ling_mun=0 if F_ct_ling_mun==.
gen share_for3_mun=F_ct_ling_mu/total_empl_mun

bysort ieland akom year: gen F_ct_geo_mun=_N if natives==0 & (pstill=="31" | pstill=="32" | pstill=="33" | pstill=="34") 
replace F_ct_geo_mun=0 if F_ct_geo_mun==.
gen share_for4_mun=F_ct_geo_mu/total_empl_mun



preserve
gen fore=natives==0
bysort cvrnr ieland year: egen tot_fore=total(fore)
bysort cvrnr year: gen tot_emp=_N
gen for_firm_bil=tot_fore/tot_emp
sum for_firm_bil
collapse for_firm_bil, by(cvrnr ieland year)

gen land2=.

destring ieland, replace

tostring land2, replace

replace land2="GL"  if ieland==5101 
replace land2="FI"  if ieland==5104
replace land2="IS"  if ieland==5105
replace land2="IS"  if ieland==5106
replace land2="CH"  if ieland==5107 
replace land2="BE" if ieland==5108
replace land2="FR" if ieland==5109
replace land2="NO"  if ieland==5110 
replace land2="SE"  if ieland==5120 
replace land2="AL"  if ieland==5122 
replace land2="AD"  if ieland==5124
replace land2="BE" if ieland==5126 
replace land2="BG"  if ieland==5128 
replace land2="CZ"  if ieland==5129
replace land2="FR" if ieland==5130 
replace land2="GR" if ieland==5134
replace land2="NL" if ieland==5140 
replace land2="IE"  if ieland==5142
replace land2="IT" if ieland==5150 
replace land2="XS" if ieland==5151
replace land2="XS"  if ieland==5152
replace land2="MT"  if ieland==5153 
replace land2="PL"  if ieland==5154 
replace land2="PT"  if ieland==5156 
replace land2="RO"  if ieland==5158
replace land2="IT"  if ieland==5159 
replace land2="CH"  if ieland==5160
replace land2="RU"  if ieland==5162 
replace land2="ES"  if ieland==5164 
replace land2="GB"  if ieland==5170 
replace land2="TR"  if ieland==5172 
replace land2="HU"  if ieland==5174  
replace land2="IT"  if ieland==5176
replace land2="DE"  if ieland==5180 
replace land2="AT"  if ieland==5182 
replace land2="DZ"  if ieland==5202 
replace land2="AO"  if ieland==5204 
replace land2="AO"  if ieland==5207 
replace land2="BI"  if ieland==5213 
replace land2="ET"  if ieland==5214 
replace land2="KM"  if ieland==5215 
replace land2="ER"  if ieland==5216 
replace land2="GM" if ieland==5222 
replace land2="GH" if ieland==5228 
replace land2="CQ" if ieland==5230  
replace land2="GW"  if ieland==5231 
replace land2="GN"  if ieland==5232  
replace land2="CV"  if ieland==5233  
replace land2="KE"  if ieland==5234
replace land2="LS"  if ieland==5235  
replace land2="LR"  if ieland==5236
replace land2="LY"  if ieland==5238  
replace land2="MZ"  if ieland==5240 
replace land2="MG"  if ieland==5242 
replace land2="ML"  if ieland==5243 
replace land2="MA"  if ieland==5244 
replace land2="MU"  if ieland==5245 
replace land2="NG"  if ieland==5246 
replace land2="NA"  if ieland==5247 
replace land2="MH" if ieland==5248  
replace land2="SL" if ieland==5255 
replace land2="SD" if ieland==5258  
replace land2="SZ" if ieland==5259 
replace land2="ZA" if ieland==5262  
replace land2="TZ"  if ieland==5266  
replace land2="TN" if ieland==5268 
replace land2="UG" if ieland==5269
replace land2="EG" if ieland==5272 
replace land2="TV" if ieland==5273 
replace land2="KI" if ieland==5274  
replace land2="VU" if ieland==5275  
replace land2="CF" if ieland==5276 
replace land2="CM" if ieland==5277  
replace land2="CG" if ieland==5278  
replace land2="CG" if ieland==5279 
replace land2="BJ" if ieland==5281  
replace land2="CI" if ieland==5282 
replace land2="GA" if ieland==5283  
replace land2="MR" if ieland==5284 
replace land2="NE" if ieland==5285 
replace land2="RW" if ieland==5287  
replace land2="SN" if ieland==5288  
replace land2="SO" if ieland==5289  
replace land2="TD" if ieland==5292   
replace land2="TG" if ieland==5293  
replace land2="BF" if ieland==5294 
replace land2="ZW" if ieland==5295  
replace land2="ZM" if ieland==5296  
replace land2="MW" if ieland==5297 
replace land2="SC" if ieland==5298 
replace land2="AR" if ieland==5302 
replace land2="BS" if ieland==5303  
replace land2="BO" if ieland==5304  
replace land2="BB" if ieland==5305  
replace land2="BR" if ieland==5306  
replace land2="GF" if ieland==5308  
replace land2="AG" if ieland==5309 
replace land2="NR" if ieland==5310 
replace land2="VC" if ieland==5311  
replace land2="CA" if ieland==5314  
replace land2="CL" if ieland==5316  
replace land2="CO" if ieland==5318 
replace land2="CR" if ieland==5322 
replace land2="CU" if ieland==5324  
replace land2="DO" if ieland==5326 
replace land2="EC" if ieland==5328  
replace land2="GT" if ieland==5338 
replace land2="GD" if ieland==5339  
replace land2="HT" if ieland==5342  
replace land2="SR" if ieland==5344  
replace land2="DM" if ieland==5345  
replace land2="LC" if ieland==5347  
replace land2="HN" if ieland==5348  
replace land2="JM" if ieland==5352 
replace land2="MX" if ieland==5354 
replace land2="NI" if ieland==5356 
replace land2="PA" if ieland==5358  
replace land2="PY" if ieland==5364  
replace land2="PE" if ieland==5366  
replace land2="SV" if ieland==5372  
replace land2="TT" if ieland==5374  
replace land2="UY" if ieland==5376 
replace land2="US" if ieland==5390  
replace land2="VE" if ieland==5392 
replace land2="IO" if ieland==5395 
replace land2="YE" if ieland==5402  
replace land2="AE" if ieland==5403 
replace land2="AF" if ieland==5404 
replace land2="BH" if ieland==5406  
replace land2="BT" if ieland==5408  
replace land2="BD" if ieland==5410  
replace land2="BN" if ieland==5412 
replace land2="MM" if ieland==5414  
replace land2="KH" if ieland==5416  
replace land2="LK" if ieland==5418  
replace land2="CY" if ieland==5422  
replace land2="TW" if ieland==5424  
replace land2="IN" if ieland==5432  
replace land2="ID" if ieland==5434  
replace land2="ID" if ieland==5435
replace land2="IQ" if ieland==5436  
replace land2="IR" if ieland==5438  
replace land2="IL" if ieland==5442  
replace land2="JP" if ieland==5444 
replace land2="JO" if ieland==5446  
replace land2="CN" if ieland==5448 
replace land2="KW" if ieland==5452 
replace land2="LA" if ieland==5454  
replace land2="LB" if ieland==5456 
replace land2="MV" if ieland==5457  
replace land2="MY" if ieland==5458  
replace land2="MN" if ieland==5459  
replace land2="OM" if ieland==5462  
replace land2="NP" if ieland==5464 
replace land2="KP" if ieland==5466  
replace land2="VN" if ieland==5468 
replace land2="PK"  if ieland==5472  
replace land2="PH" if ieland==5474 
replace land2="SA" if ieland==5478 
replace land2="SG" if ieland==5482 
replace land2="KR" if ieland==5484 
replace land2="SY" if ieland==5486  
replace land2="VN" if ieland==5488  
replace land2="TH" if ieland==5492  
replace land2="QA" if ieland==5496  
replace land2="AU" if ieland==5502  
replace land2="TO" if ieland==5505  
replace land2="FJ" if ieland==5508  
replace land2="NZ" if ieland==5514  
replace land2="US" if ieland==5522 
replace land2="DJ" if ieland==5525  
replace land2="BZ" if ieland==5526  
replace land2="PG" if ieland==5534  
replace land2="EE" if ieland==5607 
replace land2="LV" if ieland==5609 
replace land2="LT" if ieland==5611  
replace land2="ST" if ieland==5621 
replace land2="SB" if ieland==5623 
replace land2="KN" if ieland==5625 
replace land2="RU" if ieland==5700 
replace land2="UA" if ieland==5704  
replace land2="BY" if ieland==5706  
replace land2="AM" if ieland==5708 
replace land2="AZ" if ieland==5710  
replace land2="MD" if ieland==5712 
replace land2="UZ" if ieland==5714 
replace land2="KZ" if ieland==5716 
replace land2="TM" if ieland==5718 
replace land2="KG" if ieland==5720 
replace land2="TJ" if ieland==5722 
replace land2="GE" if ieland==5724 
replace land2="HR" if ieland==5750 
replace land2="SI" if ieland==5752 
replace land2="BA" if ieland==5754
replace land2="MK" if ieland==5756 
replace land2="CS" if ieland==5757 
replace land2="CS" if ieland==5758 
replace land2="CS" if ieland==5759 
replace land2="RS" if ieland==5761 
replace land2="CZ" if ieland==5776  
replace land2="SK" if ieland==5778 
replace land2="NZ" if ieland==5779 
replace land2="VN"  if ieland==5901
replace land2="FO" if ieland==5902


replace land2="FR" if land2=="MC"	
replace land2="FR" if land2=="GF"
replace land2="FR" if land2=="YT"
replace land2="FR" if land2=="MQ"
replace land2="FR" if land2=="RE"
replace land2="FR" if land2=="GP"
replace land2="BE" if land2=="LU"
replace land2="RS" if land2=="ME"
replace land2="RS" if land2=="HR"
replace land2="RS" if land2=="SI"
replace land2="RS" if land2=="MK"
replace land2="RS" if land2=="CS"
replace land2="RS" if land2=="XS"
replace land2="RS" if land2=="XK"
replace land2="RS" if land2=="BA"
replace land2="RS" if land2=="YU"
replace land2="ZR" if land2=="CD"
replace land2="ES" if land2=="AD"
replace land2="ES" if land2=="GI"
replace land2="ES" if land2=="EA"
replace land2="ES" if land2=="IC"
replace land2="ES" if land2=="XC"
replace land2="ES" if land2=="XL"
replace land2="GB" if land2=="UK"
replace land2="US" if land2=="UM"
replace land2="US" if land2=="PR"
replace land2="US" if land2=="VI"
replace land2="US" if land2=="AS"
replace land2="IT" if land2=="VA"
replace land2="IT" if land2=="SM"
replace land2="MA" if land2=="EH"
replace land2="CH" if land2=="LI"
replace land2="NO" if land2=="SJ"
replace land2="AR" if land2=="FK"
replace land2="TP" if land2=="TL"

rename land2 land

sort cvrnr land year
save ${data}for_firm_bil.dta, replace
restore


collapse F_ct F_ct_ling F_ct_geo share_for2 total_empl total_empl_mun share_for2_mun share_for3_mun share_for4_mun, by(ieland  akom year)


bysort ieland year: egen share_for2_max=max(share_for2)
drop share_for2
rename share_for2_max share_for2

sort  akom ieland
merge  akom ieland using ${data}share_1990_no_eu_off.dta
keep if _merge==3
drop if year==2012
drop _merge


sort  akom ieland
merge  akom ieland using ${data}share_1990_no_eu_off_l.dta
*keep if _merge==3
drop _merge



sort  akom ieland
merge  akom ieland using ${data}share_1990_no_eu_off_g.dta
*keep if _merge==3
drop _merge



gen F_hat_cmt=s_cm88*F_ct

gen s_hat=F_hat_cmt/P_m88

gen F_hat_cmt_ling=s_cm88_ling*F_ct_ling

gen s_hat_ling=F_hat_cmt_ling/P_m88

gen F_hat_cmt_geo=s_cm88_ling*F_ct_geo

gen s_hat_geo=F_hat_cmt_geo/P_m88

replace s_hat=0 if s_hat==.

replace s_hat_ling=0 if s_hat_ling==.
replace s_hat_geo=0 if s_hat_geo==.

gen land2=.

destring ieland, replace

tostring land2, replace

replace land2="GL"  if ieland==5101 
replace land2="FI"  if ieland==5104
replace land2="IS"  if ieland==5105
replace land2="IS"  if ieland==5106
replace land2="CH"  if ieland==5107 
replace land2="BE" if ieland==5108
replace land2="FR" if ieland==5109
replace land2="NO"  if ieland==5110 
replace land2="SE"  if ieland==5120 
replace land2="AL"  if ieland==5122 
replace land2="AD"  if ieland==5124
replace land2="BE" if ieland==5126 
replace land2="BG"  if ieland==5128 
replace land2="CZ"  if ieland==5129
replace land2="FR" if ieland==5130 
replace land2="GR" if ieland==5134
replace land2="NL" if ieland==5140 
replace land2="IE"  if ieland==5142
replace land2="IT" if ieland==5150 
replace land2="XS" if ieland==5151
replace land2="XS"  if ieland==5152
replace land2="MT"  if ieland==5153 
replace land2="PL"  if ieland==5154 
replace land2="PT"  if ieland==5156 
replace land2="RO"  if ieland==5158
replace land2="IT"  if ieland==5159 
replace land2="CH"  if ieland==5160
replace land2="RU"  if ieland==5162 
replace land2="ES"  if ieland==5164 
replace land2="GB"  if ieland==5170 
replace land2="TR"  if ieland==5172 
replace land2="HU"  if ieland==5174  
replace land2="IT"  if ieland==5176
replace land2="DE"  if ieland==5180 
replace land2="AT"  if ieland==5182 
replace land2="DZ"  if ieland==5202 
replace land2="AO"  if ieland==5204 
replace land2="AO"  if ieland==5207 
replace land2="BI"  if ieland==5213 
replace land2="ET"  if ieland==5214 
replace land2="KM"  if ieland==5215 
replace land2="ER"  if ieland==5216 
replace land2="GM" if ieland==5222 
replace land2="GH" if ieland==5228 
replace land2="CQ" if ieland==5230  
replace land2="GW"  if ieland==5231 
replace land2="GN"  if ieland==5232  
replace land2="CV"  if ieland==5233  
replace land2="KE"  if ieland==5234
replace land2="LS"  if ieland==5235  
replace land2="LR"  if ieland==5236
replace land2="LY"  if ieland==5238  
replace land2="MZ"  if ieland==5240 
replace land2="MG"  if ieland==5242 
replace land2="ML"  if ieland==5243 
replace land2="MA"  if ieland==5244 
replace land2="MU"  if ieland==5245 
replace land2="NG"  if ieland==5246 
replace land2="NA"  if ieland==5247 
replace land2="MH" if ieland==5248  
replace land2="SL" if ieland==5255 
replace land2="SD" if ieland==5258  
replace land2="SZ" if ieland==5259 
replace land2="ZA" if ieland==5262  
replace land2="TZ"  if ieland==5266  
replace land2="TN" if ieland==5268 
replace land2="UG" if ieland==5269
replace land2="EG" if ieland==5272 
replace land2="TV" if ieland==5273 
replace land2="KI" if ieland==5274  
replace land2="VU" if ieland==5275  
replace land2="CF" if ieland==5276 
replace land2="CM" if ieland==5277  
replace land2="CG" if ieland==5278  
replace land2="CG" if ieland==5279 
replace land2="BJ" if ieland==5281  
replace land2="CI" if ieland==5282 
replace land2="GA" if ieland==5283  
replace land2="MR" if ieland==5284 
replace land2="NE" if ieland==5285 
replace land2="RW" if ieland==5287  
replace land2="SN" if ieland==5288  
replace land2="SO" if ieland==5289  
replace land2="TD" if ieland==5292   
replace land2="TG" if ieland==5293  
replace land2="BF" if ieland==5294 
replace land2="ZW" if ieland==5295  
replace land2="ZM" if ieland==5296  
replace land2="MW" if ieland==5297 
replace land2="SC" if ieland==5298 
replace land2="AR" if ieland==5302 
replace land2="BS" if ieland==5303  
replace land2="BO" if ieland==5304  
replace land2="BB" if ieland==5305  
replace land2="BR" if ieland==5306  
replace land2="GF" if ieland==5308  
replace land2="AG" if ieland==5309 
replace land2="NR" if ieland==5310 
replace land2="VC" if ieland==5311  
replace land2="CA" if ieland==5314  
replace land2="CL" if ieland==5316  
replace land2="CO" if ieland==5318 
replace land2="CR" if ieland==5322 
replace land2="CU" if ieland==5324  
replace land2="DO" if ieland==5326 
replace land2="EC" if ieland==5328  
replace land2="GT" if ieland==5338 
replace land2="GD" if ieland==5339  
replace land2="HT" if ieland==5342  
replace land2="SR" if ieland==5344  
replace land2="DM" if ieland==5345  
replace land2="LC" if ieland==5347  
replace land2="HN" if ieland==5348  
replace land2="JM" if ieland==5352 
replace land2="MX" if ieland==5354 
replace land2="NI" if ieland==5356 
replace land2="PA" if ieland==5358  
replace land2="PY" if ieland==5364  
replace land2="PE" if ieland==5366  
replace land2="SV" if ieland==5372  
replace land2="TT" if ieland==5374  
replace land2="UY" if ieland==5376 
replace land2="US" if ieland==5390  
replace land2="VE" if ieland==5392 
replace land2="IO" if ieland==5395 
replace land2="YE" if ieland==5402  
replace land2="AE" if ieland==5403 
replace land2="AF" if ieland==5404 
replace land2="BH" if ieland==5406  
replace land2="BT" if ieland==5408  
replace land2="BD" if ieland==5410  
replace land2="BN" if ieland==5412 
replace land2="MM" if ieland==5414  
replace land2="KH" if ieland==5416  
replace land2="LK" if ieland==5418  
replace land2="CY" if ieland==5422  
replace land2="TW" if ieland==5424  
replace land2="IN" if ieland==5432  
replace land2="ID" if ieland==5434  
replace land2="ID" if ieland==5435
replace land2="IQ" if ieland==5436  
replace land2="IR" if ieland==5438  
replace land2="IL" if ieland==5442  
replace land2="JP" if ieland==5444 
replace land2="JO" if ieland==5446  
replace land2="CN" if ieland==5448 
replace land2="KW" if ieland==5452 
replace land2="LA" if ieland==5454  
replace land2="LB" if ieland==5456 
replace land2="MV" if ieland==5457  
replace land2="MY" if ieland==5458  
replace land2="MN" if ieland==5459  
replace land2="OM" if ieland==5462  
replace land2="NP" if ieland==5464 
replace land2="KP" if ieland==5466  
replace land2="VN" if ieland==5468 
replace land2="PK"  if ieland==5472  
replace land2="PH" if ieland==5474 
replace land2="SA" if ieland==5478 
replace land2="SG" if ieland==5482 
replace land2="KR" if ieland==5484 
replace land2="SY" if ieland==5486  
replace land2="VN" if ieland==5488  
replace land2="TH" if ieland==5492  
replace land2="QA" if ieland==5496  
replace land2="AU" if ieland==5502  
replace land2="TO" if ieland==5505  
replace land2="FJ" if ieland==5508  
replace land2="NZ" if ieland==5514  
replace land2="US" if ieland==5522 
replace land2="DJ" if ieland==5525  
replace land2="BZ" if ieland==5526  
replace land2="PG" if ieland==5534  
replace land2="EE" if ieland==5607 
replace land2="LV" if ieland==5609 
replace land2="LT" if ieland==5611  
replace land2="ST" if ieland==5621 
replace land2="SB" if ieland==5623 
replace land2="KN" if ieland==5625 
replace land2="RU" if ieland==5700 
replace land2="UA" if ieland==5704  
replace land2="BY" if ieland==5706  
replace land2="AM" if ieland==5708 
replace land2="AZ" if ieland==5710  
replace land2="MD" if ieland==5712 
replace land2="UZ" if ieland==5714 
replace land2="KZ" if ieland==5716 
replace land2="TM" if ieland==5718 
replace land2="KG" if ieland==5720 
replace land2="TJ" if ieland==5722 
replace land2="GE" if ieland==5724 
replace land2="HR" if ieland==5750 
replace land2="SI" if ieland==5752 
replace land2="BA" if ieland==5754
replace land2="MK" if ieland==5756 
replace land2="CS" if ieland==5757 
replace land2="CS" if ieland==5758 
replace land2="CS" if ieland==5759 
replace land2="RS" if ieland==5761 
replace land2="CZ" if ieland==5776  
replace land2="SK" if ieland==5778 
replace land2="NZ" if ieland==5779 
replace land2="VN"  if ieland==5901
replace land2="FO" if ieland==5902


replace land2="FR" if land2=="MC"	
replace land2="FR" if land2=="GF"
replace land2="FR" if land2=="YT"
replace land2="FR" if land2=="MQ"
replace land2="FR" if land2=="RE"
replace land2="FR" if land2=="GP"
replace land2="BE" if land2=="LU"
replace land2="RS" if land2=="ME"
replace land2="RS" if land2=="HR"
replace land2="RS" if land2=="SI"
replace land2="RS" if land2=="MK"
replace land2="RS" if land2=="CS"
replace land2="RS" if land2=="XS"
replace land2="RS" if land2=="XK"
replace land2="RS" if land2=="BA"
replace land2="RS" if land2=="YU"
replace land2="ZR" if land2=="CD"
replace land2="ES" if land2=="AD"
replace land2="ES" if land2=="GI"
replace land2="ES" if land2=="EA"
replace land2="ES" if land2=="IC"
replace land2="ES" if land2=="XC"
replace land2="ES" if land2=="XL"
replace land2="GB" if land2=="UK"
replace land2="US" if land2=="UM"
replace land2="US" if land2=="PR"
replace land2="US" if land2=="VI"
replace land2="US" if land2=="AS"
replace land2="IT" if land2=="VA"
replace land2="IT" if land2=="SM"
replace land2="MA" if land2=="EH"
replace land2="CH" if land2=="LI"
replace land2="NO" if land2=="SJ"
replace land2="AR" if land2=="FK"
replace land2="TP" if land2=="TL"

rename land2 land


collapse s_hat s_hat_geo s_hat_ling share_for2 total_empl_mun share_for2_mun share_for3_mun share_for4_mun, by(akom land year)

sort akom land year

save ${data}iv_off_dis.dta, replace




